import pyodbc
from typing import List, Dict, Any, Optional
import json


class MSSQL:
    """MSSQL数据库操作类，支持执行SQL语句和存储过程，并将结果转换为对象"""

    def __init__(self, server: str, database: str, username: str, password: str,
                 driver: str = "{ODBC Driver 17 for SQL Server}"):
        """
        初始化数据库连接参数

        Args:
            server: 服务器地址
            database: 数据库名称
            username: 用户名
            password: 密码
            driver: ODBC驱动程序
        """
        self.server = server
        self.database = database
        self.username = username
        self.password = password
        self.driver = driver
        self.connection = None

    def connect(self):
        """建立数据库连接"""
        conn_str = (
            f"DRIVER={self.driver};"
            f"SERVER={self.server};"
            f"DATABASE={self.database};"
            f"UID={self.username};"
            f"PWD={self.password}"
        )
        self.connection = pyodbc.connect(conn_str)
        return self.connection

    def disconnect(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            self.connection = None

    def execute_query(self, sql: str, params: Optional[tuple] = None) -> List[Dict[str, Any]]:
        """
        执行查询SQL语句，返回字典列表

        Args:
            sql: SQL查询语句
            params: SQL参数

        Returns:
            List[Dict[str, Any]]: 查询结果列表
        """
        if not self.connection:
            self.connect()

        cursor = self.connection.cursor()
        if params:
            cursor.execute(sql, params)
        else:
            cursor.execute(sql)

        # 获取列名
        columns = [column[0] for column in cursor.description]

        # 获取数据并转换为字典列表
        results = []
        for row in cursor.fetchall():
            results.append(dict(zip(columns, row)))

        cursor.close()
        return results

    def execute_stored_procedure(self, proc_name: str, params: Optional[tuple] = None) -> List[Dict[str, Any]]:
        """
        执行存储过程，返回结果集

        Args:
            proc_name: 存储过程名称
            params: 存储过程参数

        Returns:
            List[Dict[str, Any]]: 存储过程执行结果
        """
        if not self.connection:
            self.connect()

        cursor = self.connection.cursor()

        # 构造调用存储过程的SQL
        if params:
            placeholders = ','.join(['?' for _ in params])
            sql = f"EXEC {proc_name} {placeholders}"
            cursor.execute(sql, params)
        else:
            sql = f"EXEC {proc_name}"
            cursor.execute(sql)

        # 获取列名和数据
        if cursor.description:
            columns = [column[0] for column in cursor.description]
            results = []
            for row in cursor.fetchall():
                results.append(dict(zip(columns, row)))
            cursor.close()
            return results
        else:
            cursor.close()
            return []

    def execute_non_query(self, sql: str, params: Optional[tuple] = None) -> int:
        """
        执行非查询SQL语句（INSERT/UPDATE/DELETE）

        Args:
            sql: SQL语句
            params: SQL参数

        Returns:
            int: 受影响的行数
        """
        if not self.connection:
            self.connect()

        cursor = self.connection.cursor()
        if params:
            cursor.execute(sql, params)
        else:
            cursor.execute(sql)

        self.connection.commit()
        rowcount = cursor.rowcount
        cursor.close()
        return rowcount

    def to_objects(self, data: List[Dict[str, Any]], obj_class=None) -> List[Any]:
        """
        将字典列表转换为对象列表

        Args:
            data: 字典列表数据
            obj_class: 目标对象类（可选）

        Returns:
            List[Any]: 对象列表
        """
        if not data:
            return []

        if obj_class:
            # 转换为指定类的实例
            objects = []
            for item in data:
                obj = obj_class()
                for key, value in item.items():
                    if hasattr(obj, key):
                        setattr(obj, key, value)
                objects.append(obj)
            return objects
        else:
            # 返回原始字典列表
            return data

    def __enter__(self):
        """上下文管理器入口"""
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """上下文管理器出口"""
        self.disconnect()


# 使用示例
class User:
    """用户对象示例"""

    def __init__(self):
        self.id = None
        self.name = None
        self.email = None
        self.age = None

# 使用方式示例：
# with MSSQL("server", "database", "username", "password") as db:
#     # 执行查询
#     users_data = db.execute_query("SELECT * FROM users WHERE age > ?", (18,))
#     users = db.to_objects(users_data, User)
#
#     # 执行存储过程
#     proc_result = db.execute_stored_procedure("GetUserById", (1,))
#
#     # 执行非查询语句
#     affected_rows = db.execute_non_query("UPDATE users SET age = ? WHERE id = ?", (25, 1))
